package com.ncloud.crm.mapper;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

@Mapper
public interface ChnlDashboardMapper {
	//渠道所拥有的客户的数量
	@Select(value = ("select count(*) from (select cust_id from bs_cust where FIND_IN_SET(create_user,f_getUserList(#{userId})) "
			+ "and cust_state = '1' union select a.cust_id from bs_o_cust a,bs_o_order b  where a.order_id=b.order_id "
			+ "and FIND_IN_SET(a.create_user,f_getUserList(#{userId})))ls"))
	public String getCountChnlCust(String userId);
	//渠道客户覆盖的行业数	
	@Select(value = ("select count(distinct industry) as count from (select industry,cust_id from bs_cust"
			+ " where FIND_IN_SET(create_user,f_getUserList(#{userId})) union select industry,cust_id from bs_o_cust "
			+ "where FIND_IN_SET(create_user,f_getUserList(#{userId})))b"))
	public String getCountChnlIndustry(String userId);
	
	//渠道的总销售额
	@Select(value = ("SELECT SUM(received_fee) AS fee FROM (select discnt_id,discnt_inst_id,create_user,"
			+ "received_fee,create_time from bs_discnt_fee  UNION select c.discnt_id,c.discnt_inst_id,"
			+ "c.create_user,c.received_fee,c.create_time from bs_o_discnt_fee c,bs_o_order d where c.order_id"
			+ " = d.order_id) a WHERE FIND_IN_SET(create_user,f_getUserList(#{userId}))"))
	public String getSumFee(String userId);
		
	//当月订单收入	
	@Select(value=("select sum(received_fee) as fee from (select discnt_id,discnt_inst_id,create_user,received_fee"
			+ ",create_time from bs_discnt_fee  UNION select c.discnt_id,c.discnt_inst_id,c.create_user,"
			+ "c.received_fee,c.create_time from bs_o_discnt_fee c,bs_o_order d where c.order_id = d.order_id)"
			+ " a where FIND_IN_SET(create_user,f_getUserList(#{userId})) and DATE_FORMAT(create_time,'%Y%m') = "
			+ "DATE_FORMAT(CURDATE(),'%Y%m')"))
	public String getOrderFeeThisMonth(String userId);
	
	
	//当月结算收入	
		@Select(value=("select sum(balance) as fee from (select discnt_id,discnt_inst_id,create_user,balance"
				+ ",create_time from bs_discnt_fee  UNION select c.discnt_id,c.discnt_inst_id,c.create_user,"
				+ "c.balance,c.create_time from bs_o_discnt_fee c,bs_o_order d where c.order_id = d.order_id)"
				+ " a where FIND_IN_SET(create_user,f_getUserList(#{userId})) and DATE_FORMAT(create_time,'%Y%m') = "
				+ "DATE_FORMAT(CURDATE(),'%Y%m')"))
		public String getBalanceFeeThisMonth(String userId);
	//上月结算收入	
	@Select(value=("select sum(balance) as fee from (select discnt_id,discnt_inst_id,create_user,balance,"
			+ "create_time from bs_discnt_fee  UNION select c.discnt_id,c.discnt_inst_id,c.create_user,c.balance,"
			+ "c.create_time from bs_o_discnt_fee c,bs_o_order d where c.order_id = d.order_id) a where "
			+ "FIND_IN_SET(create_user,f_getUserList(#{userId})) and period_diff(date_format(now(),'%Y%m'),"
			+ "date_format(create_time,'%Y%m'))=1"))
	public String getBalanceLastMonth(String userId);
	
	//该用户所在渠道下销售人员数
	@Select(value = ("select count(distinct sale_id) as count from sys_sales where job_title !='1' and FIND_IN_SET(user_id,f_getUserList(#{userId}))"))
	public String getCountSaleNum(String userId);
	
	//当前渠道下的组织数
	@Select(value = ("select count(1) as count from sys_chnl a,sys_sales b where b.user_id = #{userId} and a.parent_id = b.chnl_id;"))
	public String getCountOrg(String userId);
	
	
	//渠道本月结算单总数
	@Select(value = (" select count(distinct total_id) as count from sys_chnl_bill where FIND_IN_SET(chnl_id,"
			+ "f_getChnlList(#{userId})) and bill_tag != '2' and bill_tag != '0' "))
	public String getCountChnlTotal(String userId);
	
	//渠道本月总待结算金额
	@Select(value = ("select sum(balance) as sum from sys_chnl_bill where FIND_IN_SET(chnl_id,f_getChnlList(#{userId})) and bill_tag != '2' and bill_tag != '0'"))
	public String getSumChnlBalance(String userId);
	
	//订单列表：
	@Select(value = ("SELECT a.order_id,a.fee,b.cust_id,b.cust_name,b.order_state,f_get_prodName(b.prod_id) "
			+ "AS prod_name FROM (SELECT order_id,SUM(fee) AS fee FROM sys_chnl_realbill GROUP BY order_id) "
			+ "a,(SELECT order_id,cust_id,cust_name,prod_id,order_state,create_user FROM bs_o_order UNION "
			+ " SELECT order_id,cust_id,cust_name,prod_id,order_state,create_user FROM bs_oh_order ) b WHERE "
			+ "FIND_IN_SET(b.create_user,f_getUserList(#{userId})) and a.order_id=b.order_id ORDER BY fee DESC LIMIT 5"))
	public List<Map<Object,Object>> getOrderList(String userId);

	//销售业绩列表
	@Select(value = ("SELECT user_id,name as sale_name,count,fee FROM(SELECT  lsb.id AS user_id,lsb.name,COUNT"
			+ "(DISTINCT lsa.cust_id) AS count FROM(SELECT create_user,cust_id FROM bs_cust UNION SELECT "
			+ "create_user,cust_id FROM  bs_o_cust) lsa ,sys_user lsb WHERE lsa.create_user=lsb.id GROUP BY "
			+ "lsa.create_user,lsb.name) a1 ,(SELECT create_user,SUM(received_fee) AS fee FROM  (select discnt_id"
			+ ",discnt_inst_id,create_user,received_fee,create_time from bs_discnt_fee  UNION select c.discnt_id"
			+ ",c.discnt_inst_id,c.create_user,c.received_fee,c.create_time from bs_o_discnt_fee c,bs_o_order d "
			+ "where c.order_id = d.order_id) a GROUP BY create_user) a2  WHERE a1.user_id = a2.create_user AND "
			+ "FIND_IN_SET(user_id, f_getUserList(#{userId})) ORDER BY fee DESC LIMIT 5"))
	public List<Map<Object,Object>> getSalePerformanceList(String userId);

	//该渠道下销售销售产品额
	@Select(value=("SELECT e.id AS user_id,e.name AS sale_name,sale.prod_name,sale.fee FROM sys_user e ,(SELECT"
			+ " b.create_user,f_get_prodName(a.prod_id) AS prod_name,SUM(b.received_fee) AS fee FROM (select "
			+ "prod_id,discnt_id from bs_p_discnt) a ,(select discnt_id,discnt_inst_id,create_user,received_fee,"
			+ "create_time from bs_discnt_fee  UNION select c.discnt_id,c.discnt_inst_id,c.create_user,"
			+ "c.received_fee,c.create_time from bs_o_discnt_fee c,bs_o_order d where c.order_id = d.order_id "
			+ ") b WHERE a.discnt_id = b.discnt_id  GROUP BY b.create_user,prod_name) sale WHERE sale.create_user"
			+ "=e.id and e.state='1' AND FIND_IN_SET(e.id,f_getUserList(#{userId}))"))
	public List<Map<Object,Object>> getSaleFeeProduct(String userId);
	
	//获取所有的业务  
	@Select(value = ("select distinct prod_name from bs_p_product where prod_state='1'"))
	public List<Object> getProdNameList();
	//该用户所在渠道下销售名称
	@Select(value=("SELECT a.name FROM sys_user a,sys_sales b WHERE a.id=b.user_id AND FIND_IN_SET(a.id,f_getUserList(#{0})) "
			+ "AND a.user_type ='2'  and a.state='1' ORDER BY user_id"))
	public List<Object> getSaleNameList(String userId);
	//该用户所在渠道
	@Select(value = ("select chnl_id from sys_sales where user_id = #{userId}"))
	public String getChnlIdByUserId(String userId);
	
	//查询本自然年内每月数据
	@Select(value = ("select month,sum(chnl_income) as chnl_income  from bs_chnl_income_monthly where task_id = #{0} "
			+ "and year = #{1} and find_in_set(chnl_id,f_getChildChnl(#{2})) group by month order by month asc "))
	public List<Map<Object,Object>> getSumChnlFeeByYear(String taskId , String year,String chnlId);
		
	//查询该用户所在渠道下所有销售数据
	@Select(value = ("select user_id,sale_name,prod_name,count from bs_sale_product_count  where"
			+ " FIND_IN_SET(user_id,f_getUserList(#{userId}))"))
	public List<Map<Object,Object>> getProductCount(String userId);
		
}
